<?php
namespace app\houtai\controller\user\agent;
use app\houtai\controller\BaseController;
use think\db\Expression;

class PayController extends BaseController {
  /**
   *  @brief  : 代理转账统计
   *  @return : page
   *  @details: 
   */
  public function indexAction() {
    $conds = [];
    $order = [];
    $CHANNELS = $this->GetAdminChannel();
    //////////////////////////////////////////////////////////////////////////////////////
    $channel = $this->request->param('channel/d', -1);
    if ($channel > 0 && array_key_exists($channel, $CHANNELS)) {
      $conds['n.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['n.Channel'][] = ['EQ', $channel];

    } else {
      $conds['n.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////////////////////////////////////////////////
    $search = $this->request->param('search/s', '');
    if (!empty($search)) {
      if (is_numeric($search)) {
        $search = $this->request->param('search/d', 0);
        if ($search > 0) {
          $conds['n.GameID'] = $search;
        }
      } else {
        $conds['n.Compellation'] = ['LIKE', "%{$search}%"];
      }
    }
    //////////////////////////////////////////////////////////////////////////////////////
    $orderMap = [
      'B' => 'Channel',
      'C' => 'GameID',
      'D' => 'RoomCard',
      'E' => 'BindCount',
      'F' => 'SaleTotal0',
      'G' => 'SaleTotal1',
      'H' => 'SaleTotal2',
      'I' => 'SaleTotal3',
      'J' => 'SaleTotal4',
      'K' => 'SaleTotal5',
      'L' => 'SaleTotal',
      'M' => 'SaleCount0',
      'N' => 'SaleTotal0',
      'O' => 'SaleCount1',
      'P' => 'SaleTotal1',
      'Q' => 'SaleCount2',
      'R' => 'SaleTotal2',
      'S' => 'SaleCount3',
      'T' => 'SaleTotal3',
      'U' => 'SaleCount4',
      'V' => 'SaleTotal4',
      'W' => 'SaleCount5',
      'X' => 'SaleTotal5',
      'Y' => 'SaleCount',
      'Z' => 'SaleTotal',
    ];

    $this->orderField = array_key_exists($this->orderField, $orderMap) ? $this->orderField : 'N';
    $order[$orderMap[$this->orderField]] = $this->orderDirection;
    //////////////////////////////////////////////////////////////////////////////////////
    $sql2 = table('CYRecordDB.dbo.RecordRoomCard a', 'rydb.record')->field([
      'a.SourceUserID'=>'UserID', 'COUNT([a].[RecordID])' => 'SaleCount', 'SUM([a].[RoomCard])' => 'SaleTotal',
    ])->where(['a.TypeID' => 11])->group('a.SourceUserID')->buildSql();

    $fields = [];
    $TIMES = [
      ['today', 'tomorrow'],  // 'today'
      ['yesterday', 'today'], // 'yesterday'
      ['this week 00:00:00', 'next week 00:00:00'],  // 'week'
      ['last week 00:00:00', 'this week 00:00:00'], // 'last week'
      ['first Day of this month 00:00:00', 'first Day of next month 00:00:00'], // 'month'
      ['first Day of last month 00:00:00', 'first Day of this month 00:00:00'],  // 'last month'
    ];

    foreach($TIMES as $k => &$v) {
      
      $v[0] = date('Y-m-d H:i:s', strtotime($v[0]));
      $v[1] = date('Y-m-d H:i:s', strtotime($v[1]));
      
      $fields["( SELECT COUNT ([b].[RecordID]) FROM [CYRecordDB].dbo.RecordRoomCard [b] WHERE ( [b].[SourceUserID] = [m].[UserID] ) AND [b].[CollectDate] BETWEEN '{$v[0]}' AND '{$v[1]}' )"] = "SaleCount{$k}";
      $fields["( SELECT ISNULL(SUM ([b].[RoomCard]),0) FROM [CYRecordDB].dbo.RecordRoomCard [b] WHERE ( [b].[SourceUserID] = [m].[UserID] ) AND [b].[CollectDate] BETWEEN '{$v[0]}' AND '{$v[1]}' )"] = "SaleTotal{$k}";
    }
    $list = table("{$sql2} m", 'rydb.record')->field(array_merge([
      'm.UserID', 'm.SaleCount', 'm.SaleTotal', 'n.GameID', 'n.Compellation', 'n.Channel', 'n.AgentLevel',
      'c.Sex', 'c.AccountsType', 'c.AccountImgUrl',
      "( SELECT SUM(CAST(b.RoomCard AS BIGINT)) FROM CYBasicDB.dbo.AccountsGameCurrency b WHERE b.UserID = m.UserID )"=>'RoomCard',
      "( SELECT COUNT (b.UserID) FROM CYBasicDB.dbo.AccountsInfo b WHERE b.SpreaderID = m.UserID AND b.AccountsType = 0 )"=>'BindCount',
    ], $fields))
    ->join('CYWebSMSDB.dbo.AgentReg n', 'n.UserID = m.UserID AND n.Status = 1', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsInfo c', 'c.UserID = n.UserID', 'LEFT')
    ->where($conds)->order($order)->paginate();

    //////////////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'CHANNELS'  => $CHANNELS,
      'channel'   => $channel,
      'search'    => $search,
      'list'      => $list,
      'TIMES'     => $TIMES,
    ]);
  }
  /**
   *  @brief  : 代理转账记录
   *  @return : page
   *  @details: 
   */
  public function historyAction() {
    $conds = ['a.TypeID'=>11];
    $order = [];
    $CHANNELS = $this->GetAdminChannel();
    //////////////////////////////////////////////////////////////////////////////////////
    $channel = $this->request->param('channel/d', -1);
    if ($channel > 0 && array_key_exists($channel, $CHANNELS)) {
      $conds['b.Channel'][] = ['IN', array_keys($CHANNELS)];
      $conds['b.Channel'][] = ['EQ', $channel];
    } else {
      $conds['b.Channel'] = ['IN', array_keys($CHANNELS)];
    }
    //////////////////////////////////////////////////////
    $search1 = $this->request->param('search1/s', '', 'trim');
    if (!empty($search1)) {
      if (is_numeric($search1)) {
        $search1 = $this->request->param('search1/d', 0);
        $conds['a.SourceUserID|b.GameID'] = $search1;
      } else {
        $conds['b.Compellation'] = ['LIKE', "%{$search1}%"];
      }
    }
    //////////////////////////////////////////////////////
    $search2 = $this->request->param('search2/s', '', 'trim');
    if (!empty($search2)) {
      if (is_numeric($search2)) {
        $search2 = $this->request->param('search2/d', 0);
        $conds['a.TargetUserID|c.GameID'] = $search2;
      } else {
        $conds['c.NickName'] = ['LIKE', "%{$search2}%"];
      }
    }
    //////////////////////////////////////////////////////
    $time1 = $this->request->param('time1/s', '', 'trim');
    $time2 = $this->request->param('time2/s', '', 'trim');

    $aa = YmdHis2Unix($time1);
    $bb = YmdHis2Unix($time2);
    $ta = date('Y-m-d 00:00:00', $aa);
    $tb = date('Y-m-d 23:59:59', $bb);
    if ($aa > 0) {
      if ($bb > 0) {
        if ($aa <= $bb) {
          $conds['a.CollectDate'] = ['BETWEEN TIME', [$ta, $tb]];
        } else {
          $conds['a.CollectDate'] = ['>= TIME', $ta];
        }
      } else {
        $conds['a.CollectDate'] = ['>= TIME', $ta];
      }
    } else {
      if ($bb > 0) {
        $conds['a.CollectDate'] = ['<= TIME', $tb];
      }
    }
    //////////////////////////////////////////////////////
    $orderMap = [
      'B' => 'SChannel',
      'C' => 'SBeforeCard',
      'D' => 'TBeforeCard',
      'E' => 'RoomCard',
      'F' => 'CollectDate',
      'G' => 'ClientIP',
      'H' => 'SGameID',
      'I' => 'SBeforeCard',
      'J' => 'TGameID',
      'K' => 'TBeforeCard',
    ];
    $this->orderField = array_key_exists($this->orderField, $orderMap) ? $this->orderField : 'F';
    $order[$orderMap[$this->orderField]] = $this->orderDirection;
    //////////////////////////////////////////////////////////////////////////////////////
    $list = table('CYRecordDB.dbo.RecordRoomCard a', 'rydb.record')->field([
      'a.RecordID',
      'a.SourceUserID' => 'SUserID',
      'a.TargetUserID' => 'TUserID',
      'a.SBeforeCard',
      'a.TBeforeCard',
      'a.RoomCard',
      'a.TypeID',
      'a.ClientIP',
      'a.CollectDate',
      'b.AccountImgUrl' => 'SHeadImgUrl',
      'c.AccountImgUrl' => 'THeadImgUrl',
      'b.AccountsType' => 'SAccountsType',
      'c.AccountsType' => 'TAccountsType',
      'b.GameID' => 'SGameID',
      'c.GameID' => 'TGameID',
      'b.Channel' => 'SChannel',
      'c.Channel' => 'TChannel',
      'c.NickName' => 'TNickName',
      'b.Compellation' => 'SCompellation',
    ])
    ->join('CYBasicDB.dbo.AccountsInfo b', 'b.UserID = a.SourceUserID', 'LEFT')
    ->join('CYBasicDB.dbo.AccountsInfo c', 'c.UserID = a.TargetUserID', 'LEFT')
    ->where($conds)->order($order)->paginate();
    //////////////////////////////////////////////////////////////////////////////////////
    return $this->fetch('', [
      'CHANNELS'=> $CHANNELS,
      'channel' => $channel,
      'list'    => $list,
      'search1' => $search1,
      'search2' => $search2,
      'time1'   => $time1,
      'time2'   => $time2,
    ]);
  }
}
